package com.statistics.util;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Set;

/**
 * 各单位成果类型年度统计表 导出工具类
 **/
public class AchExportUtils {

    public static void exportExcel(HttpServletResponse response, JSONArray data, String fileName) throws Exception {
        setResponse(response, fileName);
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet(fileName != null ? fileName : "Sheet1");

            if (data == null || data.isEmpty()) {
                workbook.write(response.getOutputStream());
                return;
            }

            JSONObject headers = data.getJSONObject(0);
            createHeaderRow(sheet, headers, createHeaderStyle(workbook));
            fillDataRows(sheet, data, createTotalStyle(workbook));
            sheet.autoSizeColumn(1);
            workbook.write(response.getOutputStream());
        }
    }

    static void setResponse(HttpServletResponse response, String fileName) throws Exception {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + encodeFileName(fileName) + ".xlsx");
    }

    private static String encodeFileName(String fileName) throws Exception {
        return URLEncoder.encode(fileName, "UTF-8");
    }

    // 创建表头样式
    static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font dataFont = workbook.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 11);
        dataFont.setBold(true);
        dataFont.setColor(IndexedColors.WHITE.index);
        style.setFont(dataFont);
        return style;
    }

    // 创建数据样式
    static CellStyle createTotalStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = workbook.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 11);
        style.setFont(totalFont);
        return style;
    }

    // 生成表头行
    private static void createHeaderRow(Sheet sheet, JSONObject headers, CellStyle style) {
        Row categoryRow = sheet.createRow(0);
        Row yearRow = sheet.createRow(1);

        createHeaderCell(categoryRow, 0, "序号", style);
        createHeaderCell(categoryRow, 1, "部门", style);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));

        JSONObject paper = headers.getJSONObject("paper");
        Set<String> strings = paper.keySet();

        String[] ach = {"学术论文", "学术著作", "研究报告", "鉴定报告", "艺术作品", "专利", "著作权", "标准", "药证", "新品种", "主办会议",
                "学术讲座", "参加会议", "人员派遣", "人员接受"};
        int colIndex = 2;
        for (String str : ach) {
            createHeaderCell(categoryRow, colIndex, str, style);
            if (strings.size() != 1) {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + strings.size() - 1));
            }
            for (String string : strings) {
                createHeaderCell(yearRow, colIndex, string, style);
                colIndex++;
            }
        }
    }

    private static void createHeaderCell(Row row, int col, String value, CellStyle style) {
        // 创建表头单元格
        Cell cell = row.createCell(col);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    // 填充数据行
    private static void fillDataRows(Sheet sheet, JSONArray jsonArray, CellStyle style) {
        int rowIndex = 2;
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject data = jsonArray.getJSONObject(i);
            Row row = sheet.createRow(rowIndex++);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(i + 1);
            cell0.setCellStyle(style);
            Cell cell1 = row.createCell(1);
            cell1.setCellValue(data.getString("unitName"));
            cell1.setCellStyle(style);
            String[] ach = {"paper", "book", "researchReport", "appraisalProduct", "artProduct", "patent", "copyright", "standard", "medicine", "breed", "meeting",
                    "lecture", "joinMeeting", "personnelDispatch", "personnelAccept"};
            int colIndex = 2;
            for (String str : ach) {
                JSONObject paper = data.getJSONObject(str);
                for (String string : paper.keySet()) {
                    Cell cell = row.createCell(colIndex++);
                    setCellValue(cell, paper.getString(string));
                    cell.setCellStyle(style);
                }
            }
        }

    }

    // 处理不同类型的数据
    static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value));
        } else {
            cell.setCellValue(value.toString());
        }
    }
}